#!/usr/bin/python
#coding=utf-8
from sqlalchemy.types import VARCHAR, DECIMAL
import pandas as pd
import datetime
import numpy as np
from common import df_operator as op, db_utils as util
import sys
#from xpy3lib.utils import db_utils as util
from xpy3lib.XRetryableQuery import XRetryableQuery
from xpy3lib.XRetryableSave import XRetryableSave
from sicost.AbstractDPJob import AbstractDPJob
from sicost.config import app_config
from xpy3lib.XLogger import XLogger
from xpy3lib.utils.XUtils import XUtils
# profile = 'dev'


dtypedict = {'COST_CENTER': VARCHAR,'UNIT_CODE': VARCHAR,'CLASS': VARCHAR,
            'SHIFT': VARCHAR,'DATE': VARCHAR,'PRODUCE_START_TIME': VARCHAR,'PRODUCE_END_TIME': VARCHAR,
            'IND': VARCHAR,'IND_NAME': VARCHAR,'IND_UNIT': VARCHAR,
            'INDEX_VALUE': DECIMAL,'DATA_TYPE': VARCHAR,
            'REC_CREATOR': VARCHAR, 'REC_CREATE_TIME': VARCHAR, 'REC_REVISOR': VARCHAR,'REC_REVISOR_TIME': VARCHAR
            }

def initMO0201(cost_center,unit_code,work_time):
    work_time_str = work_time.strftime("%Y%m%tmp_dict%H%M%S")
    if len(work_time_str) == 14:
        if work_time_str[8:10] >= '08' and work_time_str[8:10] < '20':
            start_time = work_time_str[0:8]+"080000"
            end_time = work_time_str[0:8]+"200000"
        elif work_time_str[8:10] >= '20':
            start_time = work_time_str[0:8] + "200000"
            end_time = (work_time + datetime.timedelta(days=1)).strftime("%Y%m%tmp_dict") + "200000"
        elif work_time_str[8:10] < '08':
            start_time = (work_time - datetime.timedelta(days=1)).strftime("%Y%m%tmp_dict") + "200000"
            end_time = work_time_str[0:8] + "080000"
    elif len(work_time_str) == 6:
        start_time = datetime.strptime(work_time_str[0:5] + "01",'%Y%m%tmp_dict')#先转成当月
        start_time = (start_time - datetime.timedelta(hours=1)).strftime("%Y%m%tmp_dict") + "200000" #前一天晚上20点
        end_time = work_time_str[0:5] + "31200000" #当月最后一天
    elif len(work_time_str) == 4:
        start_time = (datetime.strptime(work_time_str[0:3] , '%Y') - datetime.timedelta(years=1)).strftime("%Y")+'1231200000'
        end_time = work_time_str[0:3] +  '1231200000'

    #先删除这段时间的数据
    if len(work_time_str) == 14:
       del_sql = """delete from bgtaras1.t_ads_fact_sicb_mo0201 WHERE COST_CENTER='""" + cost_center + """'  and UNIT_CODE ='""" + unit_code + """' and DATA_TYPE = '0' and PRODUCE_START_TIME = '""" + str(start_time) + """' AND PRODUCE_END_TIME = '""" + str(end_time) + """'"""
    else:
        del_sql = """delete from bgtaras1.t_ads_fact_sicb_mo0201 WHERE COST_CENTER='""" + cost_center + """'  and UNIT_CODE ='""" + unit_code + """' and DATA_TYPE = '0' and PRODUCE_START_TIME = '""" + str(
            start_time) + """' AND PRODUCE_END_TIME = '""" + str(end_time) + """' and DATE = '"""+ work_time_str +"""'"""
    print(del_sql)
    conn_rds.execute(del_sql)
    # db_conn_rds.execute(del_sql)
    # query_sql = """select IND,CALC_LOGIC as sql_m,DATA_SOURCE_1 from BGTARAS1.T_ADS_WH_SICB_MO0201A WHERE  UNIT_CODE =  '"""+ unit_code +"""'  ORDER BY SEQ"""
    # df = pd.read_sql(query_sql, conn_rds)
    # for tup in zip(df['ind'], df['sql_m'], df['data_source_1']):
    #     sql_m = op.replaceColumn(str(tup[1]), ["P_DATA_TYPE", "P_UNIT", "P_COST_CENTER",  "P_START_TIME", "P_END_TIME", "P_WORK_TIME", "P_WORK_TIME"],
    #                                    ["0", unit_code,cost_center,start_time,end_time,work_time_str,work_time_str])
    #     # print(sql_m)
    #     df_fact = util.query(conn=conn_rds if tup[2] == "RDS" else conn_db2, sql=sql_m)
    #     print(sql_m)
    #     util.insert(df_fact, conn_rds, "T_ADS_FACT_SICB_MO0201", "BGTARAS1", dtypedict)
    #     # XRetryableSave(p_db_conn=db_conn_rds, p_table_name='T_ADS_FACT_SICB_MO0201', p_schema='BGTARAS1',
    #     #                p_dataframe=df_fact,
    #     #                p_max_times=5).redo()
    query_sql = """select IND,COL_PROC_1 as sql_m1,COL_PROC_2 as sql_m2,DATA_SOURCE_1,DATA_SOURCE_2,CALC_LOGIC  from BGTARAS1.T_ADS_WH_SICB_MO0201C WHERE  UNIT_CODE =  '""" + unit_code + """'  ORDER BY REC_ID"""
    print(query_sql)
    df = pd.read_sql(query_sql, conn_rds)
    for tup in zip(df['ind'], df['sql_m1'], df['data_source_1'],df['sql_m2'], df['data_source_2']):

        sql_m2 = op.replaceColumn(str(tup[3]),
                                 ["P_DATA_TYPE", "P_UNIT", "P_COST_CENTER", "P_START_TIME", "P_END_TIME", "P_WORK_TIME",
                                  "P_WORK_TIME"],
                                 ["0", unit_code, cost_center, start_time, end_time, work_time_str, work_time_str])
        df_fact2 = util.query(conn=conn_rds if tup[4] == "RDS" else conn_db2, sql=sql_m2)
        success = df_fact2.empty is False
        if success is False:
            continue
        df_fact2.columns = df_fact2.columns.str.upper()
        df_fact2['PROD_COILNO'] = df_fact2['PROD_COILNO'].astype(str)
        l = df_fact2['PROD_COILNO'].values.tolist()
        p_mat_str = "'%s'" % ("','".join(l))

        sql_m1 = op.replaceColumn(str(tup[1]),
                                  ["P_DATA_TYPE", "P_UNIT", "P_COST_CENTER", "P_START_TIME", "P_END_TIME",
                                   "P_WORK_TIME",
                                   "P_WORK_TIME", "P_MAT_STR"],
                                  ["0", unit_code, cost_center, start_time, end_time, work_time_str, work_time_str, p_mat_str])
        print(sql_m1)
        df_fact1 = util.query(conn=conn_rds if tup[2] == "RDS" else conn_db2, sql=sql_m1)
        success = df_fact1.empty is False
        if success is False:
            continue
        df_fact1.columns = df_fact1.columns.str.upper()
        df_fact = pd.merge(df_fact2, df_fact1, on=['UNIT_CODE', 'PROD_COILNO'], how='left')
        v = ['COST_CENTER',
             'UNIT_CODE',
             'CLASS',
             'SHIFT']
        df_fact['VALUE'] = df_fact['VALUE'].astype('float64')
        a = df_fact.groupby(v)['VALUE'].agg([np.sum])
        a.rename(columns={'sum': 'INDEX_VALUE'}, inplace=True)
        df_fact.drop_duplicates(subset=v, keep='first', inplace=True)
        df_fact_new = pd.merge(a, df_fact, on=v, how='left')
        df_fact_new.drop(['VALUE'], axis=1, inplace=True)
        df_fact_new.drop(['PROD_COILNO'], axis=1, inplace=True)
        util.insert(df_fact_new, conn_rds, "T_ADS_FACT_SICB_MO0201", "BGTARAS1", dtypedict)

def MO0201(cost_center,unit_code,work_time):
    global conn_db2, conn_rds
    conn_rds = util.getConnectionDb2('10.70.48.121', '50000 ', 'RG0PROD', 'bgtaroq000', 'bgtaroq000bgta')
    conn_db2 = util.getConnectionDb2('10.70.16.170', '50170 ', 'dbprode7', 'dbprode7', 'dbprode7')

    MO0201(cost_center, unit_code, work_time)
    conn_rds.close()
    conn_db2.close()

if __name__ == '__main__':
    '''先读配置表
      再获取RDS连接
    '''
    now_time = datetime.datetime.now()
    # start_time = '20210321200000'
    # end_time = '20210322200000'
    global conn_db2, conn_rds
    conn_rds = util.getConnectionDb2('10.70.48.121', '50000 ', 'RG0PROD', 'bgtaroq000', 'bgtaroq000bgta')
    conn_db2 = util.getConnectionDb2('10.70.16.170', '50170 ', 'dbprode7', 'dbprode7', 'dbprode7')
    # config = app_config[profile]
    # try:
    #     db_conn_mpp = util.getConnectionDb2(config.DB_HOST_MPP_DB2,
    #                                         config.DB_PORT_MPP_DB2,
    #                                         config.DB_DBNAME_MPP_DB2,
    #                                         config.DB_USER_MPP_DB2,
    #                                         config.DB_PASSWORD_MPP_DB2)
    #     XLogger.getInstance().logger.info('connect db_conn_mpp success')
    #
    #     db_conn_dbprod7 = util.getConnectionDb2(config.DB_HOST_DBPROD7_DB2,
    #                                             config.DB_PORT_DBPROD7_DB2,
    #                                             config.DB_DBNAME_DBPROD7_DB2,
    #                                             config.DB_USER_DBPROD7_DB2,
    #                                             config.DB_PASSWORD_DBPROD7_DB2)
    #     XLogger.getInstance().logger.info('connect db_conn_dbprod7 success')
    #     db_conn_rds = util.getConnectionDb2(config.DB_HOST_RDS_DB2,
    #                                         config.DB_PORT_RDS_DB2,
    #                                         config.DB_DBNAME_RDS_DB2,
    #                                         config.DB_USER_RDS_DB2,
    #                                         config.DB_PASSWORD_RDS_DB2)
    #     XLogger.getInstance().logger.info('connect db_conn_rds success')
    # except Exception as e:
    #     db_conn_mpp = None
    #     db_conn_rds = None
    #     db_conn_dbprod7 = None
    #     XLogger.getInstance().logger.critical(str(e))


    initMO0201("MIDD", "Q418", now_time)
    conn_rds.close()
    conn_db2.close()
